﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;
using ChairtyBank.DAL;

namespace ChairtyBank.DAL
{
    class ProjectEntity
    {
        public Int32 Id { get; set; }
        
        public String Title { get; set; }
        public String Place { get; set; }
        public String Type { get; set; }
        public String Establish { get; set; }
        public DateTime DateOfBirth { get; set; }
        }
    }

    /*
 
        Class Client which contains all methods related to Customer table
 
    */


    class Project
    {
        //Set absolute DB path rather than relative to solve openFileDialog reset path problem
        private static string conString = ConfigurationManager.ConnectionStrings["ChairtyBank"].ToString();
        private static string cmdText = "";





         public static List<ProjectEntity> GetAllProjectss()
        {
            ProjectEntity p = null;
            List<ProjectEntity> projects = new List<ProjectEntity>();

            cmdText = "select * from Projects ";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);

            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    p= fillEntityFromReader(reader);
                    projects.Add(p);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return projects;

        }

        public static ProjectEntity GetProject(int id)
        {
            ProjectEntity p = null;


            cmdText = "select * from Projects where Project_ID=@Project_ID";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Project_ID", id);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if(reader.Read())
                {
                    p = fillEntityFromReader(reader);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if(con.State == ConnectionState.Open)
                    con.Close();
            }

            return p;
        }

        public static Int32 Add(ProjectEntity p)
        {
            cmdText = "insert into Projects (Project_Title,Project_Place,Project_type,Project_Establish )values(@Project_Title,@Project_Place,@Project_type,@Project_Establish) SELECT SCOPE_IDENTITY()";
            Int32 projectID = -1;
              
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            
            cmd.Parameters.AddWithValue("@Project_Title", p.Title);
            cmd.Parameters.AddWithValue("@Project_Place", p.Place);
            cmd.Parameters.AddWithValue("@Project_type", p.Type);
            cmd.Parameters.AddWithValue("@Project_Establish", p.Establish);
            try
            {
                con.Open();
                projectID = Convert.ToInt32(cmd.ExecuteScalar());

            }
            catch(Exception ex){
                MessageBox.Show("Exception==>>"+ex.Message);
            }
            finally
            {
                if(con.State == ConnectionState.Open)
                    con.Close();

            }

            return projectID;

        }





        public static bool Update(ProjectEntity p)
        {
            Boolean flag = false;
            cmdText = "UPDATE Projects SET Project_Title=@Project_Title , Project_Place=@Project_Place ,Project_type= @Project_type ,Project_Establish=@Project_Establish  WHERE Project_ID=@Project_ID";
            
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            
            cmd.Parameters.AddWithValue("@Project_Title", p.Title);
            cmd.Parameters.AddWithValue("@Project_Place", p.Place);
            cmd.Parameters.AddWithValue("@Project_type", p.Type);
            cmd.Parameters.AddWithValue("@Project_Establish", p.Establish);
            cmd.Parameters.AddWithValue("@Project_ID", p.Id);

            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                //MessageBox.Show(numOfRow + "Record updated");
                flag = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;
        }


             /*
                Delete using id or Serial 
             */

        public static bool Delete(ProjectEntity p)
        {
            Boolean flag = false;
            cmdText = "Delete from Projects WHERE Project_ID=@Project_ID";

        
                SqlConnection con = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand(cmdText, con);
                cmd.Parameters.AddWithValue("@Project_ID", p.Id);
                try
                {
                    con.Open();
                    Int32 numOfRow = cmd.ExecuteNonQuery();
                    flag = true;


                }
                catch (Exception ex)
                {
                    MessageBox.Show("Exception==>>" + ex.Message);
                }
                finally
                {
                    if (con.State == ConnectionState.Open)
                        con.Close();
                }
                return flag;
        
        }














        private static ProjectEntity fillEntityFromReader(SqlDataReader reader)
        {
            ProjectEntity p = new ProjectEntity();
            p.Id = Int32.Parse(reader["Project_ID"].ToString());
           if (reader["Project_Title"] != null)
                p.Title = reader["Project_Title"].ToString();
            if (reader["Project_Place"] != null)
                p.Place = reader["Project_Place"].ToString();
            if (reader["Project_type"] != null)
                p.Type = reader["Project_type"].ToString();
            if (reader["Project_Establish"] != null)
                p.Establish = reader["Project_Establish"].ToString();

           
            return p;
        }

         
    }
 